<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
 * Class	: M_do
 * 
 * Table	: do
 *  
 * @author Eko Junaidi Salam
 *
 */
class M_do extends CI_Model{

	function __construct(){
		parent::__construct();
	}
	
	/**
	 * Fungsi	: getAll
	 * 
	 * Untuk mengambil all-data
	 * 
	 * @param number $start
	 * @param number $page
	 * @param number $limit
	 * @return json
	 */
	
	function getRPTSales($tglmulai, $tglsampai,$saring,$filters,$start, $page, $limit){
		if($saring == "Range" && $filters == null)
		{
			// GridFilters sends filters as an Array if not json encoded
			if (is_array($filters)) {
				$encoded = false;
			} else {
				$encoded = true;
				$filters = json_decode($filters);
			}

			$where = " (TANGGAL_LPB >= DATE('$tglmulai') AND TANGGAL_LPB <= DATE('$tglsampai')) ";
			$qs = '';

			// loop through filters sent by client
			if (is_array($filters)) {
				for ($i=0;$i<count($filters);$i++){
					$filter = $filters[$i];

					// assign filter data (location depends if encoded or not)
					if ($encoded) {
						$field = $filter->field;
							
						$value = $filter->value;
						$compare = isset($filter->comparison) ? $filter->comparison : null;
						$filterType = $filter->type;
					} else {
						$field = $filter['field'];
						$value = $filter['data']['value'];
						$compare = isset($filter['data']['comparison']) ? $filter['data']['comparison'] : null;
						$filterType = $filter['data']['type'];
					}

					switch($filterType){
						case 'string' : $qs .= " AND ".$field." LIKE '%".$value."%'"; Break;
						case 'list' :
							if (strstr($value,',')){
								$fi = explode(',',$value);
								for ($q=0;$q<count($fi);$q++){
									$fi[$q] = "'".$fi[$q]."'";
								}
								$value = implode(',',$fi);
								$qs .= " AND ".$field." IN (".$value.")";
							}else{
								$qs .= " AND ".$field." = '".$value."'";
							}
						Break;
						case 'boolean' : $qs .= " AND ".$field." = ".($value); Break;
						case 'numeric' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = ".$value; Break;
								case 'lt' : $qs .= " AND ".$field." < ".$value; Break;
								case 'gt' : $qs .= " AND ".$field." > ".$value; Break;
							}
						Break;
						case 'date' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = '".date('Y-m-d',strtotime($value))."'"; Break;
								case 'lt' : $qs .= " AND ".$field." < '".date('Y-m-d',strtotime($value))."'"; Break;
								case 'gt' : $qs .= " AND ".$field." > '".date('Y-m-d',strtotime($value))."'"; Break;
							}
						Break;
						case 'datetime' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
								case 'lt' : $qs .= " AND ".$field." < '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
								case 'gt' : $qs .= " AND ".$field." > '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
							}
						Break;
					}
				}
				$where .= $qs;
			}
			
			$sql = "SELECT * FROM TERIMABARANG
			WHERE ".$where;
			//$sql .= " ORDER BY ".$dsort;
			$sql .= " LIMIT ".$start.",".$limit;
			$query = $this->db->query($sql)->result();
			
			$total  = $this->db->query("SELECT count(TANGGAL_LPB) as total
			FROM TERIMABARANG
			WHERE ".$where)->result();
			
			$data   = array();
			foreach($query as $result){
				$data[] = $result;
			}
			
			$json	= array(
				'success'   => TRUE,
				'message'   => "Loaded data",
				'total'     => $total[0]->total,
				'data'      => $data
			);
			
			return $json;
		}

	}
	function getPembelianGlobal($tglmulai, $tglsampai,$saring,$filters,$start, $page, $limit){
		if($saring == "Range" && $filters == null)
		{
			// GridFilters sends filters as an Array if not json encoded
			if (is_array($filters)) {
				$encoded = false;
			} else {
				$encoded = true;
				$filters = json_decode($filters);
			}

			$where = " (TANGGAL_LPB >= DATE('$tglmulai') AND TANGGAL_LPB <= DATE('$tglsampai')) ";
			$qs = '';

			// loop through filters sent by client
			if (is_array($filters)) {
				for ($i=0;$i<count($filters);$i++){
					$filter = $filters[$i];

					// assign filter data (location depends if encoded or not)
					if ($encoded) {
						$field = $filter->field;
							
						$value = $filter->value;
						$compare = isset($filter->comparison) ? $filter->comparison : null;
						$filterType = $filter->type;
					} else {
						$field = $filter['field'];
						$value = $filter['data']['value'];
						$compare = isset($filter['data']['comparison']) ? $filter['data']['comparison'] : null;
						$filterType = $filter['data']['type'];
					}

					switch($filterType){
						case 'string' : $qs .= " AND ".$field." LIKE '%".$value."%'"; Break;
						case 'list' :
							if (strstr($value,',')){
								$fi = explode(',',$value);
								for ($q=0;$q<count($fi);$q++){
									$fi[$q] = "'".$fi[$q]."'";
								}
								$value = implode(',',$fi);
								$qs .= " AND ".$field." IN (".$value.")";
							}else{
								$qs .= " AND ".$field." = '".$value."'";
							}
						Break;
						case 'boolean' : $qs .= " AND ".$field." = ".($value); Break;
						case 'numeric' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = ".$value; Break;
								case 'lt' : $qs .= " AND ".$field." < ".$value; Break;
								case 'gt' : $qs .= " AND ".$field." > ".$value; Break;
							}
						Break;
						case 'date' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = '".date('Y-m-d',strtotime($value))."'"; Break;
								case 'lt' : $qs .= " AND ".$field." < '".date('Y-m-d',strtotime($value))."'"; Break;
								case 'gt' : $qs .= " AND ".$field." > '".date('Y-m-d',strtotime($value))."'"; Break;
							}
						Break;
						case 'datetime' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
								case 'lt' : $qs .= " AND ".$field." < '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
								case 'gt' : $qs .= " AND ".$field." > '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
							}
						Break;
					}
				}
				$where .= $qs;
			}
			
			$sql = "SELECT * FROM TERIMABARANG
			WHERE ".$where;
			//$sql .= " ORDER BY ".$dsort;
			$sql .= " LIMIT ".$start.",".$limit;
			$query = $this->db->query($sql)->result();
			
			$total  = $this->db->query("SELECT count(TANGGAL_LPB) as total
			FROM TERIMABARANG
			WHERE ".$where)->result();
			
			$data   = array();
			foreach($query as $result){
				$data[] = $result;
			}
			
			$json	= array(
				'success'   => TRUE,
				'message'   => "Loaded data",
				'total'     => $total[0]->total,
				'data'      => $data
			);
			
			return $json;
		}
	}
	
	function getHutangPjkPembelian($tglmulai, $tglsampai,$saring,$filters,$start, $page, $limit){
		if($saring == "Range" && $filters == null)
		{
			// GridFilters sends filters as an Array if not json encoded
			if (is_array($filters)) {
				$encoded = false;
			} else {
				$encoded = true;
				$filters = json_decode($filters);
			}

			$where = " (TANGGAL_LPB >= DATE('$tglmulai') AND TANGGAL_LPB <= DATE('$tglsampai')) ";
			$qs = '';

			// loop through filters sent by client
			if (is_array($filters)) {
				for ($i=0;$i<count($filters);$i++){
					$filter = $filters[$i];

					// assign filter data (location depends if encoded or not)
					if ($encoded) {
						$field = $filter->field;
							
						$value = $filter->value;
						$compare = isset($filter->comparison) ? $filter->comparison : null;
						$filterType = $filter->type;
					} else {
						$field = $filter['field'];
						$value = $filter['data']['value'];
						$compare = isset($filter['data']['comparison']) ? $filter['data']['comparison'] : null;
						$filterType = $filter['data']['type'];
					}

					switch($filterType){
						case 'string' : $qs .= " AND ".$field." LIKE '%".$value."%'"; Break;
						case 'list' :
							if (strstr($value,',')){
								$fi = explode(',',$value);
								for ($q=0;$q<count($fi);$q++){
									$fi[$q] = "'".$fi[$q]."'";
								}
								$value = implode(',',$fi);
								$qs .= " AND ".$field." IN (".$value.")";
							}else{
								$qs .= " AND ".$field." = '".$value."'";
							}
						Break;
						case 'boolean' : $qs .= " AND ".$field." = ".($value); Break;
						case 'numeric' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = ".$value; Break;
								case 'lt' : $qs .= " AND ".$field." < ".$value; Break;
								case 'gt' : $qs .= " AND ".$field." > ".$value; Break;
							}
						Break;
						case 'date' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = '".date('Y-m-d',strtotime($value))."'"; Break;
								case 'lt' : $qs .= " AND ".$field." < '".date('Y-m-d',strtotime($value))."'"; Break;
								case 'gt' : $qs .= " AND ".$field." > '".date('Y-m-d',strtotime($value))."'"; Break;
							}
						Break;
						case 'datetime' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
								case 'lt' : $qs .= " AND ".$field." < '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
								case 'gt' : $qs .= " AND ".$field." > '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
							}
						Break;
					}
				}
				$where .= $qs;
			}
			
			$sql = "SELECT * FROM TERIMABARANG
			WHERE ".$where;
			//$sql .= " ORDER BY ".$dsort;
			$sql .= " LIMIT ".$start.",".$limit;
			$query = $this->db->query($sql)->result();
			
			$total  = $this->db->query("SELECT count(TANGGAL_LPB) as total
			FROM TERIMABARANG
			WHERE ".$where)->result();
			
			$data   = array();
			foreach($query as $result){
				$data[] = $result;
			}
			
			$json	= array(
				'success'   => TRUE,
				'message'   => "Loaded data",
				'total'     => $total[0]->total,
				'data'      => $data
			);
			
			return $json;
		}
	}
	 
	function getPendingDO($start, $page, $limit){
		$sql = "SELECT t1.NOMOR_DO,pocd.NOMOR_PO,pocd.NAMA_BARANG,pocd.JUMLAH as POCD_QTY, t1.QUANTITY as DOD_QTY, 
				pocd.JUMLAH-IF(NOT ISNULL(t1.QUANTITY),t1.QUANTITY,0) as QUANTITY,pocd.HARGA_SATUAN
				FROM poclientdet pocd
				LEFT JOIN (
					SELECT dod.NOMOR_PO,dod.NOMOR_DO,dod.NAMA_BARANG,sum(dod.QUANTITY) as QUANTITY
					FROM dodetail dod
					GROUP BY dod.NAMA_BARANG
				) as t1
				ON t1.NOMOR_PO=pocd.NOMOR_PO AND t1.NAMA_BARANG=POCD.NAMA_BARANG
				WHERE pocd.JUMLAH <> t1.QUANTITY or ISNULL(t1.QUANTITY)";
				
		$query = $this->db->query($sql)->result();
		$total = $this->db->query($sql)->num_rows();
		
		$data   = array();
		foreach($query as $result){
			$data[] = $result;
		}
		
		$json	= array(
						'success'   => TRUE,
						'message'   => "Loaded data",
						'total'     => $total,
						'data'      => $data
		);
		
		return $json;
	}
	
	function getAll($start, $page, $limit){
		$query  = $this->db->limit($limit, $start)->order_by('ID', 'DESC')->get('do')->result();
		$total  = $this->db->get('do')->num_rows();
		
		$data   = array();
		foreach($query as $result){
			$data[] = $result;
		}
		
		$json	= array(
						'success'   => TRUE,
						'message'   => "Loaded data",
						'total'     => $total,
						'data'      => $data
		);
		
		return $json;
	}
	
	/**
	 * Fungsi	: save
	 * 
	 * Untuk menambah data baru atau mengubah data lama
	 * 
	 * @param array $data
	 * @return json
	 */
	function save($data){
		$last   = NULL;
		
		$pkey = array('NOMOR_DO'=>$data->NOMOR_DO);
		$cek = $this->db->get_where('do', $pkey);
		if($cek->num_rows() > 0){
			/*
			 * Data Exist
			 */
			
			$cek = $cek->result();
			
			// $tmp = substr($data->SUB_TOTAL,3,strlen($data->SUB_TOTAL));
			// $tmp = str_replace('.','',$tmp);
			// $tmp = str_replace(',','.',$tmp);
			// $data->SUB_TOTAL = $tmp;
			// $tmp = substr($data->TOTAL,3,strlen($data->TOTAL));
			// $tmp = str_replace('.','',$tmp);
			// $tmp = str_replace(',','.',$tmp);
			// $data->TOTAL = $tmp;	
			 
			$arrdatau = array('TANGGAL_DO'=>(strlen(trim($data->TANGGAL_DO)) > 0 ? date('Y-m-d', strtotime($data->TANGGAL_DO)) : NULL),'NOMOR_PO'=>$data->NOMOR_PO,'NOMOR_INVOICE'=>$data->NOMOR_INVOICE,'DRIVER'=>$data->DRIVER,'NOMOR_VEHICLE'=>$data->NOMOR_VEHICLE,'CURRENCY'=>$data->CURRENCY,'KURS'=>$data->KURS,'SUB_TOTAL'=>$data->SUB_TOTAL,'PPN'=>$data->PPN,'MATERAI'=>$data->MATERAI,'TOTAL'=>$data->TOTAL,'KODE_CUSTOMER'=>$data->KODE_CUSTOMER,'NAMA_CUSTOMER'=>$data->NAMA_CUSTOMER,'CONTACT_PERSON'=>$data->CONTACT_PERSON,'ALAMAT_CUSTOMER'=>$data->ALAMAT_CUSTOMER,'TELPON_CUSTOMER'=>$data->TELPON_CUSTOMER,'FAX_CUSTOMER'=>$data->FAX_CUSTOMER,'APA_KAPSUL'=>$data->APA_KAPSUL,'JATUH_TEMPO'=>(strlen(trim($data->JATUH_TEMPO)) > 0 ? date('Y-m-d', strtotime($data->JATUH_TEMPO)) : NULL),'DIBUAT_OLEH'=>$data->DIBUAT_OLEH,'JUMLAH_EDIT'=>($cek[0]->JUMLAH_EDIT + 1),'TANGGAL_EDIT'=>(strlen(trim($data->TANGGAL_EDIT)) > 0 ? date('Y-m-d', strtotime($data->TANGGAL_EDIT)) : NULL),'STATUS'=>$data->STATUS,'INFO'=>$data->INFO,'POSTING'=>$data->POSTING);
			 
			$this->db->where($pkey)->update('do', $arrdatau);
			$last   = $data;
			
		}else{
			/*
			 * Data Not Exist
			 * 
			 * Process Insert
			 */
			 // $tmp = substr($data->SUB_TOTAL,3,strlen($data->SUB_TOTAL));
			// $tmp = str_replace('.','',$tmp);
			// $tmp = str_replace(',','.',$tmp);
			// $data->SUB_TOTAL = $tmp;
			// $tmp = substr($data->TOTAL,3,strlen($data->TOTAL));
			// $tmp = str_replace('.','',$tmp);
			// $tmp = str_replace(',','.',$tmp);
			// $data->TOTAL = $tmp;
			
			$jenis = ($data->CURRENCY == 'USD'?'valas':($data->APA_KAPSUL == '1'?'capsul':'bbaku'));
			
			$kode = $this->m_public_function->GenCode($jenis);
			
			$arrdatac = array('NOMOR_DO'=>$kode->NOMOR_DO,'TANGGAL_DO'=>(strlen(trim($data->TANGGAL_DO)) > 0 ? date('Y-m-d', strtotime($data->TANGGAL_DO)) : NULL),'NOMOR_PO'=>$data->NOMOR_PO,'NOMOR_INVOICE'=>$kode->NOMOR_INVOICE,'DRIVER'=>$data->DRIVER,'NOMOR_VEHICLE'=>$data->NOMOR_VEHICLE,'CURRENCY'=>$data->CURRENCY,'KURS'=>$data->KURS,'SUB_TOTAL'=>$data->SUB_TOTAL,'PPN'=>$data->PPN,'MATERAI'=>$data->MATERAI,'TOTAL'=>$data->TOTAL,'KODE_CUSTOMER'=>$data->KODE_CUSTOMER,'NAMA_CUSTOMER'=>$data->NAMA_CUSTOMER,'CONTACT_PERSON'=>$data->CONTACT_PERSON,'ALAMAT_CUSTOMER'=>$data->ALAMAT_CUSTOMER,'TELPON_CUSTOMER'=>$data->TELPON_CUSTOMER,'FAX_CUSTOMER'=>$data->FAX_CUSTOMER,'APA_KAPSUL'=>$data->APA_KAPSUL,'JATUH_TEMPO'=>(strlen(trim($data->JATUH_TEMPO)) > 0 ? date('Y-m-d', strtotime($data->JATUH_TEMPO)) : NULL),'DIBUAT_OLEH'=>$data->DIBUAT_OLEH,'JUMLAH_EDIT'=>$data->JUMLAH_EDIT,'TANGGAL_EDIT'=>(strlen(trim($data->TANGGAL_EDIT)) > 0 ? date('Y-m-d', strtotime($data->TANGGAL_EDIT)) : NULL),'STATUS'=>$data->STATUS,'INFO'=>$data->INFO,'POSTING'=>$data->POSTING);
			 
			$this->db->insert('do', $arrdatac);
			$last   = $this->db->where($pkey)->get('do')->row();
			
			$rs = $this->db->where(array('param' => ($jenis == 'valas'?'DOV':($jenis == 'capsul'?'DOC':'DONC'))))->get('init')->result();
			$no = intval(trim($rs[0]->value))+1;
			$this->db->where(array('param' => ($jenis == 'valas'?'DOV':($jenis == 'capsul'?'DOC':'DONC'))))->update('init', array('value' => $no));
			
			$rs = $this->db->where(array('param' => ($jenis == 'valas'?'DOV':($jenis == 'capsul'?'INVC':'INVNC'))))->get('init')->result();
			$no = intval(trim($rs[0]->value))+1;
			$this->db->where(array('param' => ($jenis == 'valas'?'INVNC':($jenis == 'capsul'?'INVC':'INVNC'))))->update('init', array('value' => $no));
			
		}
		
		$total  = $this->db->get('do')->num_rows();
		
		$json   = array(
						"success"   => TRUE,
						"message"   => 'Data berhasil disimpan',
						'total'     => $total,
						"data"      => $last
		);
		
		return $json;
	}
	
	/**
	 * Fungsi	: delete
	 * 
	 * Untuk menghapus satu data
	 * 
	 * @param array $data
	 * @return json
	 */
	function delete($data){
		$pkey = array('NOMOR_DO'=>$data->NOMOR_DO,'ID'=>$data->ID);
		
		$this->db->where($pkey)->delete('do');
		
		$total  = $this->db->get('do')->num_rows();
		$last = $this->db->get('do')->result();
		
		$json   = array(
						"success"   => TRUE,
						"message"   => 'Data berhasil dihapus',
						'total'     => $total,
						"data"      => $last
		);				
		return $json;
	}
}
?>